USE DS2012
GO

IF OBJECT_ID('CarIdle') IS NOT NULL
	DROP TABLE CarIdle
GO

IF OBJECT_ID('CarArrival') IS NOT NULL
	DROP TABLE CarArrival
GO

IF OBJECT_ID('CarsStatistic') IS NOT NULL
	DROP TABLE CarsStatistic
GO

IF OBJECT_ID('usp_CarsStatistic_get') IS NOT NULL
	DROP PROCEDURE usp_CarsStatistic_get
GO

CREATE TABLE CarsStatistic (
	CarsStatistic_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	CarNumber int NOT NULL
	) ON [PRIMARY]
GO

CREATE TABLE CarIdle (
	CarIdle_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	StartDate DateTime NOT NULL,
	EndDate DateTime NOT NULL,
	DownTime AS DATEDIFF(mi,StartDate,EndDate),
	IdleStation_ID int NOT NULL REFERENCES Stations(ID),
	CarsStatistic_ID int NOT NULL REFERENCES CarsStatistic(CarsStatistic_ID)
	) ON [PRIMARY]
GO

CREATE TABLE CarArrival (
	CarArrival_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	ArrivalDate DateTime NOT NULL,
	StartDate DateTime NOT NULL,
	TravelTime AS DATEDIFF(mi,StartDate,ArrivalDate),
	ArrivalStation_ID int NOT NULL REFERENCES Stations(ID),
	CarsStatistic_ID int NOT NULL REFERENCES CarsStatistic(CarsStatistic_ID)
	) ON [PRIMARY]
GO

CREATE PROCEDURE usp_CarsStatistic_get
AS SET NOCOUNT ON
BEGIN
	INSERT INTO CarsStatistic
		SELECT DISTINCT Dislocation.CarNumber 
		FROM Dislocation
		LEFT JOIN CarsStatistic ON CarsStatistic.CarNumber = Dislocation.CarNumber
		WHERE CarsStatistic.CarsStatistic_ID IS NULL
	
	DECLARE @I int
	DECLARE @J int
	DECLARE @End int
	DECLARE @Idle int
	DECLARE @IdleEnd int
	DECLARE @tempCarsStatistic TABLE
		(tempCarsStatistic_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
		 StartDate DateTime NULL,
		 ArrivalStationID int NULL,
		 OperationDate DateTime NULL, 
		 StationOfOperationID int NULL)
	
	SET @I = 1
	SET @J = 1
	
	WHILE EXISTS(SELECT * FROM CarsStatistic WHERE CarsStatistic_ID = @I) BEGIN
		INSERT INTO @tempCarsStatistic
			(StartDate,ArrivalStationID,OperationDate,StationOfOperationID)
			SELECT StartDate,ArrivalStationID,OperationDate,StationOfOperationID
			FROM Dislocation
			LEFT JOIN CarsStatistic ON CarsStatistic_ID = @I
			WHERE Dislocation.CarNumber = CarsStatistic.CarNumber 
			ORDER BY OperationDate 
			
		SET @End = @J
		SET @Idle = @J
		SET @IdleEnd = 0
		
		SET @J += 1;
		
		WHILE EXISTS(SELECT * FROM @tempCarsStatistic WHERE tempCarsStatistic_ID = @J)  BEGIN
			IF NOT EXISTS(SELECT T1.ArrivalStationID
							FROM @tempCarsStatistic AS T1
							LEFT JOIN @tempCarsStatistic AS T2 ON T2.tempCarsStatistic_ID = @End
							WHERE T1.tempCarsStatistic_ID = @J AND T2.ArrivalStationID = T1.ArrivalStationID)
				SET @End = @J
			
			IF EXISTS(SELECT T1.StationOfOperationID
						FROM @tempCarsStatistic AS T1
						LEFT JOIN @tempCarsStatistic AS T2 ON T2.tempCarsStatistic_ID = @Idle
						WHERE T1.tempCarsStatistic_ID = @J AND T2.StationOfOperationID = T1.StationOfOperationID)
				SET @IdleEnd = @J
			
			ELSE BEGIN
				IF EXISTS(SELECT T1.StationOfOperationID
							FROM @tempCarsStatistic AS T1
							LEFT JOIN @tempCarsStatistic AS T2 ON T2.tempCarsStatistic_ID = @End
							WHERE T1.tempCarsStatistic_ID = @J AND T2.ArrivalStationID = T1.StationOfOperationID)
				
					INSERT INTO CarArrival (ArrivalDate,StartDate,ArrivalStation_ID,CarsStatistic_ID)
						SELECT T1.OperationDate,T2.StartDate,T2.ArrivalStationID,@I
						FROM @tempCarsStatistic AS T1
						LEFT JOIN @tempCarsStatistic AS T2 ON T2.tempCarsStatistic_ID = @End
						WHERE T1.tempCarsStatistic_ID = @J 
				
				IF @IdleEnd != 0 
					INSERT INTO CarIdle (StartDate,EndDate,IdleStation_ID,CarsStatistic_ID)
						SELECT T1.OperationDate, T0.OperationDate, T1.StationOfOperationID, @I 
						FROM @tempCarsStatistic AS T0
						LEFT JOIN @tempCarsStatistic AS T1 ON T1.tempCarsStatistic_ID = @Idle
						WHERE T0.tempCarsStatistic_ID = @IdleEnd
						
				SET @Idle = @J
				SET @IdleEnd = 0
			END
			
			SET @J += 1
		END
		
		IF @IdleEnd != 0 
				INSERT INTO CarIdle (StartDate,EndDate,IdleStation_ID,CarsStatistic_ID)
					SELECT T1.OperationDate, T0.OperationDate, T1.StationOfOperationID, @I 
					FROM @tempCarsStatistic AS T0
					LEFT JOIN @tempCarsStatistic AS T1 ON T1.tempCarsStatistic_ID = @Idle
					WHERE T0.tempCarsStatistic_ID = @IdleEnd
		
		SET @I += 1
	END
END
GO